﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;
using REPORT.DTO;
namespace REPORT.DAL
{
    public class DAL_KHACHHANG
    {
        DataTable dt = new DataTable();
        public DataTable LoadKH()
        {
            DbProvider db = new DbProvider();
            string sql =@"SELECT * FROM KHACHHANG";
            try
            {
                dt = db.ExecuteQuery(sql);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            return dt;
        }
        public int ThemKH(DTO_KHACHHANG obj)
        {
            DbProvider db = new DbProvider();
            int result = 0;
            string strInsert = @"INSERT INTO KHACHHANG (ten_khach_hang,dia_chi,so_dien_thoai) VALUES(@tenkh,@dichi,@sdt)";
            db.AddParameter(new SqlParameter("@tenkh", obj.HoTenKhachHang));
            db.AddParameter(new SqlParameter("@dichi", obj.DiaChi));
            db.AddParameter(new SqlParameter("@sdt", obj.SoDienThoai));
            try
            {
                    result = db.ExcuteNoneQuery(strInsert);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            return result;
        }
        public int SuaKH(DTO_KHACHHANG obj)
        {
            DbProvider db = new DbProvider();
            int result = 0;
            string strInsert = @"UPDATE KHACHHANG SET ten_khach_hang=@tenkh,dia_chi=@diachi,so_dien_thoai=@sdt WHERE ma_khach_hang=@makh";
            db.AddParameter(new SqlParameter("@tenkh", obj.HoTenKhachHang));
            db.AddParameter(new SqlParameter("@diachi", obj.DiaChi));
            db.AddParameter(new SqlParameter("@sdt", obj.SoDienThoai));
            db.AddParameter(new SqlParameter("@makh", obj.MaKhachHang));
            //try
            //{
                result = db.ExcuteNoneQuery(strInsert);
            //}
            //catch (Exception ex) { throw new Exception(ex.Message); }
            return result;
        }
        public int XoaKH(DTO_KHACHHANG obj)
        {
            DbProvider db = new DbProvider();
            int result = 0;
            string strDelete = @"DELETE KHACHHANG WHERE ma_khach_hang = @makh";
            db.AddParameter(new SqlParameter("@makh", obj.MaKhachHang));
            //try
            //{
                    result = db.ExcuteNoneQuery(strDelete);
            //}
            //catch (Exception ex) { throw new Exception(ex.Message); }
            return result;
        }
        public DataTable IdMax()
        {
            DataTable dt = new DataTable();
            DbProvider db = new DbProvider();
            string sql = @"select * from khachhang where ma_khach_hang=(SELECT IDENT_CURRENT( 'khachhang'))";
            try
            {
                dt = db.ExecuteQuery(sql);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            return dt;
        }
        public DataTable AutoTenKH()
        {
            DataTable dt = new DataTable();
            DbProvider db = new DbProvider();
            string sql = @"select ten_khach_hang from khachhang";
            try
            {
                dt = db.ExecuteQuery(sql);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            return dt;
        }
        public SqlDataReader LayMaKH(string tenkh)
        {
            SqlDataReader sqlrd;
            DbProvider db = new DbProvider();
            string sql = @"select * from khachhang where ten_khach_hang=N'" + tenkh + "'";
            try
            {
                sqlrd = db.ExcuteReader(sql);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            return sqlrd;
        }
        public DataTable ThongTinKhachHang(int makh)
        {
            DataTable dt = new DataTable();
            DbProvider db = new DbProvider();
            string sql = @"select * from khachhang where ma_khach_hang=N'" + makh + "'";
            try
            {
                dt = db.ExecuteQuery(sql);
            }
            catch (Exception ex) { throw new Exception(ex.Message); }
            return dt;
        }
        
    }
}
